package edu.ucla.cs.cs144;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.lucene.analysis.Analyzer;
import org.apache.lucene.analysis.standard.StandardAnalyzer;
import org.apache.lucene.document.Document;
import org.apache.lucene.queryParser.ParseException;
import org.apache.lucene.queryParser.QueryParser;
import org.apache.lucene.search.Hit;
import org.apache.lucene.search.Hits;
import org.apache.lucene.search.IndexSearcher;
import org.apache.lucene.search.Query;
import org.apache.lucene.search.BooleanClause;
import org.apache.lucene.search.BooleanQuery;
import org.apache.lucene.search.TermQuery;

import org.apache.lucene.index.Term;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.text.SimpleDateFormat;

import edu.ucla.cs.cs144.DbManager;
import edu.ucla.cs.cs144.SearchConstraint;
import edu.ucla.cs.cs144.SearchResult;
import edu.ucla.cs.cs144.Tag;

public class AuctionSearch implements IAuctionSearch {
	static final String LUCENE_INDEX = "LUCENE_INDEX";
	static final String INDEX_NAME = "ebay-search";
	
	String[] months = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" };

	/* 
         * You will probably have to use JDBC to access MySQL data
         * Lucene IndexSearcher class to lookup Lucene index.
         * Read the corresponding tutorial to learn about how to use these.
         *
         * Your code will need to reference the directory which contains your
	 * Lucene index files.  Make sure to read the environment variable 
         * $LUCENE_INDEX with System.getenv() to build the appropriate path.
	 *
	 * You may create helper functions or classes to simplify writing these
	 * methods. Make sure that your helper functions are not public,
         * so that they are not exposed to outside of this class.
         *
         * Any new classes that you create should be part of
         * edu.ucla.cs.cs144 package and their source files should be
         * placed at src/edu/ucla/cs/cs144.
         *
         */
	
	public SearchResult[] basicSearch(String query, int numResultsToSkip, 
			int numResultsToReturn) {
		// TODO: Your code here!

		SearchResult[] result = new SearchResult[0];

		try 
		{
			IndexSearcher searcher = new IndexSearcher(System.getenv(LUCENE_INDEX) + "/" + INDEX_NAME);
			QueryParser parser = new QueryParser("content", new StandardAnalyzer());

			Query q = parser.parse(query);        
			Hits hits = searcher.search(q);

			// Allocate the results array
			if (numResultsToReturn != 0 && numResultsToReturn - numResultsToSkip < hits.length())
				result = new SearchResult[numResultsToReturn];
			else
				result = new SearchResult[hits.length()];
			
			Iterator<Hit> iter =  hits.iterator();
			int index = 0;
			
			// Grab the necessary results from hits
			// Factoring in the number of results to skip and the number of results to return
			while(iter.hasNext() && (numResultsToReturn == 0 || (index < hits.length() && index - numResultsToSkip < numResultsToReturn)))
			{
				if (index > numResultsToSkip - 1) {
					Hit hit = iter.next();
					Document doc = hit.getDocument();
					result[index] = new SearchResult(doc.get(Columns.ITEMID), doc.get(Columns.NAME));
				}
				
				index++;
			}
		} 
		catch (Exception e) 
		{
			System.out.println("Exception caught.\n");
			e.printStackTrace();
		}
		
		return result;
	}

	public SearchResult[] advancedSearch(SearchConstraint[] constraints, 
			int numResultsToSkip, int numResultsToReturn) {
		// TODO: Your code here!
		ArrayList<SearchResult> result_set = new ArrayList<SearchResult>();
		ArrayList<SearchResult> final_set = new ArrayList<SearchResult>();
		
		SearchResult[] result = new SearchResult[0];
		
		ArrayList<SearchConstraint> lucene = new ArrayList<SearchConstraint>();
		ArrayList<SearchConstraint> mysql = new ArrayList<SearchConstraint>();
		
		String lucene_query_string = "";
		String mysql_query_string = "";
		
		HashMap<String, String> name_conversion = new HashMap<String, String>();
		name_conversion.put(FieldName.ItemName, Tag.NAME);
		name_conversion.put(FieldName.Category, Tag.CATEGORY);
		name_conversion.put(FieldName.SellerId, Tag.SELLER);
		name_conversion.put(FieldName.BuyPrice, Tag.BUY_PRICE);
		name_conversion.put(FieldName.BidderId, Tag.Bids.Bid.BIDDER);
		name_conversion.put(FieldName.EndTime, Tag.ENDS);
		name_conversion.put(FieldName.Description, Tag.DESCRIPTION);
		
		// Build the query strings
		for (int i = 0; i < constraints.length; i++) {			
			String fieldname = constraints[i].getFieldName();
			if (name_conversion.containsKey(fieldname)) {
				fieldname = name_conversion.get(fieldname);
			}

			if (constraints[i].getFieldName().equals(FieldName.EndTime)) {
				constraints[i].setValue(convertToSQL(constraints[i].getValue()));
			}
			
			if (constraints[i].getFieldName().equals(FieldName.ItemName) || 
				constraints[i].getFieldName().equals(FieldName.Category) || 
				constraints[i].getFieldName().equals(FieldName.Description)) {
				if (!lucene_query_string.equals(""))
					lucene_query_string += " AND ";
					lucene_query_string += fieldname + ":\"" + encode(constraints[i].getValue()) + "\"";
			} else {
				if (!mysql_query_string.equals(""))
					mysql_query_string += " AND ";
					mysql_query_string += fieldname + " = \"" + encode(constraints[i].getValue()) + "\"";
			}
		}

		System.out.println("Lucene Query String:  " + lucene_query_string);
		System.out.println("MySQL Query String:   " + mysql_query_string);
		
		Hits lucene_results = null;
		if (!lucene_query_string.equals("")) {
			try 
			{
				// Get the Lucene results
				IndexSearcher searcher = new IndexSearcher(System.getenv(LUCENE_INDEX) + "/" + INDEX_NAME);
				QueryParser parser = new QueryParser("content", new StandardAnalyzer());
			
				Query q = parser.parse(lucene_query_string);        
				lucene_results = searcher.search(q);

				// System.out.println(lucene_results.length());
				
				// Get the Lucene results in the ArrayList to be compared with the MySQL Results
				Iterator<Hit> iter = lucene_results.iterator();
				while (iter.hasNext()) {
					Hit hit = iter.next();
					Document doc = hit.getDocument();
					result_set.add(new SearchResult(doc.get(Columns.ITEMID), doc.get(Columns.NAME)));
				}
			}
			catch (Exception e) 
			{
				e.printStackTrace();
			}
		}
		
		ResultSet mysql_results = null;
		if (!mysql_query_string.equals("")) {
			try 
			{
				Connection conn = MyDBManager.getConnection(true);
				Statement statement = conn.createStatement();
				
				// Get the MySQL results
				mysql_results = statement.executeQuery("SELECT ItemID, Name FROM Items WHERE " + mysql_query_string);
				
				// Merge the two sets of returned results
				while (mysql_results.next()) {
					SearchResult sr = new SearchResult(mysql_results.getString(Columns.ITEMID), mysql_results.getString(Columns.NAME));
					System.out.println(sr.getItemId());
					if (lucene_results == null || hasSearchResult(result_set, sr)) {
						final_set.add(sr);
					}
				}
				
				mysql_results.close();
				statement.close();
				
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} else {
			final_set = result_set;
		}
		
		System.out.println(final_set.size());
		
		// Allocate the results array
		if (numResultsToReturn != 0 && numResultsToReturn - numResultsToSkip < final_set.size())
			result = new SearchResult[numResultsToReturn];
		else
			result = new SearchResult[final_set.size()];
		
		for (int i = 0; i < (numResultsToReturn == 0 ? result.length : numResultsToReturn); i++) {
			if (i + numResultsToSkip < result.length)
				result[i] = final_set.get(i + numResultsToSkip);
		}
		
		return result;
	}

	public String getXMLDataForItemId(String itemId) {
		// TODO: Your code here!
		String xml = "";
		
		Connection conn = null;

		// create a connection to the database
		try 
		{
			conn = MyDBManager.getConnection(true);
			
			Statement statement = conn.createStatement();
			
			// Get the Item from the Items table
			ResultSet result = statement.executeQuery(
						"SELECT * FROM Items" + 
						" WHERE " + "Items." + Columns.ITEMID + " = \"" + itemId + "\"");
			
			result.first();
			if (result.getRow() != 0) {				
				xml += "<Item ItemID=\"" + itemId + "\">\n";		// Parse Item tag
				xml += "  " + getXMLLine(Tag.NAME, result);			// Parse Name tag
				
				// Parse Category tag
				// First, get the Categories
				Statement category_statement = conn.createStatement();
				ResultSet category_result = category_statement.executeQuery(
							"SELECT Category FROM CatItems, Categories" + 
							" WHERE " + "CatItems." + Columns.ITEMID + " = \"" + itemId + "\" AND "
									   + "CatItems." + Columns.CATEGORYID + " = " + "Categories." + Columns.CATEGORYID);
				while (category_result.next()) {
					xml += "  " + getXMLLine(Tag.CATEGORY, category_result);
				}
				
				category_result.close();
				category_statement.close();
				
				xml += "  " + getXMLLine(Tag.CURRENTLY, result);		// Parse Currently tag
				
				if (!getXMLLine(Tag.BUY_PRICE, result).equals("<Buy_Price>$0.00</Buy_Price>\n"))
					xml += "  " + getXMLLine(Tag.BUY_PRICE, result);	// Parse Buy Price tag
				
				xml += "  " + getXMLLine(Tag.FIRST_BID, result);		// Parse First Bid tag
				xml += "  " + getXMLLine(Tag.NUMBER_OF_BIDS, result);	// Parse Number of Bids tag
				
				// Parse Bids tag
				// First, get the Bids
				Statement bids_statement = conn.createStatement();
				ResultSet bids_result = bids_statement.executeQuery(
							"SELECT Bids.UserID, Amount, BidTime, Rating, Location, Country FROM Bids, Users " + 
							" WHERE " + "Bids." + Columns.ITEMID + " = \"" + itemId + "\" AND "
									  + "Bids." + Columns.USERID + " = Users." + Columns.USERID);
				xml += "  " + "<" + Tag.BIDS + ">\n";
				while (bids_result.next()) {
					try {
						xml += "    <" + Tag.Bids.BID + ">\n";
						xml += "      <" + Tag.Bids.Bid.BIDDER + " UserID=\"" + bids_result.getString("UserID") + "\" ";
						xml += "Rating=\"" + bids_result.getString("Rating") + "\">\n";
						try {
							xml += "        " + getXMLLine(Tag.Bids.Bid.LOCATION, bids_result);	// Find Bidder's Location if present
						} catch (SQLException e) {}
						try {
							xml += "        " + getXMLLine(Tag.Bids.Bid.COUNTRY, bids_result);	// Find Bidder's Country if present
						} catch (SQLException e) {}
						xml += "      </" + Tag.Bids.Bid.BIDDER + ">\n";
						// NOTE:  Time is a special case since it was entered in the database as "BidTime"
						xml += "      <" + Tag.Bids.TIME + ">" + formatDate(bids_result.getString("BidTime")) + "</" + Tag.Bids.TIME + ">\n";
						xml += "      " + getXMLLine(Tag.Bids.AMOUNT, bids_result);
						xml += "    </" + Tag.Bids.BID + ">\n";
					} catch (SQLException e) {}
				}
				xml += "  </" + Tag.BIDS + ">\n";
				bids_result.close();
				bids_statement.close();
				
				// Get the Seller data
				Statement seller_statement = conn.createStatement();
				ResultSet seller_result = seller_statement.executeQuery(
							"SELECT UserID, Rating, Location, Country FROM Items, Users " + 
							" WHERE " + "Items." + Columns.ITEMID + " = \"" + itemId + "\" AND "
									  + "Items." + Columns.SELLER + " = Users." + Columns.USERID);
				seller_result.first();

				xml += "  " + getXMLLine(Tag.LOCATION, seller_result);	// Parse Location tag
				xml += "  " + getXMLLine(Tag.COUNTRY, seller_result);	// Parse Country tag
				
				xml += "  " + getXMLLine(Tag.STARTED, result);			// Parse Started tag
				xml += "  " + getXMLLine(Tag.ENDS, result);				// Parse Ends tag
				
				xml += "  <" + Tag.SELLER + " "; 						// Parse Seller tag
				xml += "UserID" + "=\"" + seller_result.getString("UserID") + "\" ";
				xml += "Rating=\"" + seller_result.getString("Rating") + "\"/>\n";
				
				xml += "  " + getXMLLine(Tag.DESCRIPTION, result);		// Parse Description tag
				
				xml += "</" + Tag.ITEM + ">";
				
				seller_result.close();
				seller_statement.close();
			}

			result.close();
			statement.close();

			conn.close();
		}
		catch (SQLException ex) 
		{
			System.out.println(ex);
			ex.printStackTrace();
			xml = "";
		}

		return xml;
	}
	
	private String getXMLLine(String tag, ResultSet r) throws SQLException {
		String xml = "<" + tag + ">";
		
		// Add $ if necessary
		if (tag.equals(Tag.CURRENTLY) || tag.equals(Tag.BUY_PRICE) || tag.equals(Tag.FIRST_BID)  || tag.equals(Tag.Bids.AMOUNT))
			xml += "$";

		// Format for either string or date
		if (tag.equals(Tag.STARTED) || tag.equals(Tag.ENDS) || tag.equals(Tag.Bids.TIME))
			xml += formatDate(r.getString(tag));
		else
			xml += encode(r.getString(tag));
		
		xml += "</" + tag + ">\n";
		return xml;
	}
	
	// Find if the SearchResult is in the ArrayList
	private boolean hasSearchResult(ArrayList<SearchResult> l, SearchResult sr) {
		for (int i = 0; i < l.size(); i++)
			if (l.get(i).getItemId().equals(sr.getItemId()) && l.get(i).getName().equals(sr.getName()))
				return true;
		return false;
	}
	
	// Encodes a string with escape characters
	private String encode(String s) {
		
		return s.replaceAll("\"", "quot;")
				.replaceAll("\'", "&apos;")
				.replaceAll("&", "&amp;")
				.replaceAll("<", "&lt;")
				.replaceAll(">", "&gt;");
	}
	
	// Formats a string in the format of the XML
	private String formatDate(String s) {
		int month = Integer.parseInt(s.substring(5, 7)) - 1;
		
		String date = "";
		
		date += months[month];						// Format month
		date += s.substring(7, 10);					// Format day
		date += "-";
		date += s.substring(2, 4);					// Format year
		date += s.substring(10, s.length() - 2);	// Format time
		
		return date;
	}
	
	/* Takes in a date string from the eBay data and converts it into a format that MySQL can understand and parse
	*/
	private String convertToSQL(String date) {
		int month = 0;
		int century = 20;
		
		// Find the month
		for (month = 0; month < months.length; month++)
			if (months[month] == date.substring(0, 3))
				break;
				
		// Naively get the century
		if ((new Integer(date.substring(7, 9))).intValue() > 11)
			century = 19;

		// Format the output
		String output = "";
		output += String.valueOf(century);
		output += date.substring(7, 9);
		output += "-";
		if (month < 10)
			output += "0";
		output += String.valueOf(month);
		output += "-";
		output += date.substring(4, 6);
		output += date.substring(9);
		
		return output;
	}
	
	public String echo(String message) {
		return message;
	}

}
